﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Convert.DataAccess;
using Convert.LOTSmodel;
using Convert.CPIAMmodel;
using System.Configuration;
using System.Data.OleDb;
namespace Convert.DBconverter
{
    class SalesHistoryConverter
    {
        public static string LotsConnectionString; //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.lotsConnectionString"].ConnectionString;
        public static string CpiamConnectionString; //= ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.CPIAMPOSMaxRetailConnectionString"].ConnectionString;

        public static void ConvertSalesHistory(string lotsConnectionString, string cpiamConnectionString)
        {
            LotsConnectionString = lotsConnectionString;
            CpiamConnectionString = cpiamConnectionString;
            DataTable DeptGroupsDT = DataTables.CreateDeptGroupsDT();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT History.StockID, Format(History.[Month], 'MM, yyyy') AS MonthYear, SUM(History.QtySold) AS Qty, SUM(History.ValueSold) AS SaleValue, SUM(History.TotalCost) AS TotalCost, Stock.PackSize
                        FROM (History INNER JOIN
                        Stock ON History.StockID = Stock.StockID)
                        GROUP BY History.StockID, Format(History.[Month], 'MM, yyyy'), Stock.PackSize
                        ORDER BY Format(History.[Month], 'MM, yyyy')");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        try
                        {
                            History history = new History();
                            int i = 0;
                            history.StockID = reader.GetInt32(i);
                            history.MonthYear = reader.GetString(++i);
                            history.Qty = reader.GetDouble(++i);
                            history.SaleValue = reader.IsDBNull(++i) ? 0 : System.Convert.ToInt64(reader.GetValue(i));
                            history.TotalCost = reader.IsDBNull(++i) ? 0 : System.Convert.ToInt64(reader.GetValue(i));
                            history.PackSize = reader.IsDBNull(++i) ? 1 : reader.GetFloat(i);
                            Update(history);
                        }
                        catch (Exception ex)
                        {
                            Logger.LogWarningAndError("Error in Convert SalesHistory ", ex);
                        }
                    }
                }
                dbConn.Close();
        }

        private static void Update(History history)
        {
            DateTime strCurMnthTag = DateTime.Now;
            int intCurYear = 0;
            int intHistYear = 0;
            int intCurMnth = 0;
            int intHistMnth = 0;
            float sigPackSize = 1;
            decimal decSalesValue = 0;
            decimal decTotalCost = 0;

            intCurYear = int.Parse(strCurMnthTag.Year.ToString());
            intHistYear = int.Parse(history.MonthYear.Substring(history.MonthYear.IndexOf(",") + 1).Trim());
            intCurMnth = int.Parse(strCurMnthTag.Month.ToString());
            intHistMnth = int.Parse(history.MonthYear.Substring(0, history.MonthYear.IndexOf(",")).Trim());

            if (history.Qty > 0)
            {

                if (history.PackSize > 0)
                    sigPackSize = history.PackSize;
                else
                    sigPackSize = 1;
                decSalesValue = history.SaleValue / 100;
                decTotalCost = history.TotalCost / 100;
                if ((intCurYear == intHistYear) && (intCurMnth == intHistMnth))
                {
                    UpdateStocksCurrentMnthSalesDetails(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                }
                else
                {
                    if (intHistYear == intCurYear)
                    {
                        if (intHistMnth == 1)
                        {
                            UpdateStockCurrYearMnth1(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                        else if (intHistMnth == 2)
                        {
                            UpdateStockCurrYearMnth2(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                        else if (intHistMnth == 3)
                        {
                            UpdateStockCurrYearMnth3(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                        else if (intHistMnth == 4)
                        {
                            UpdateStockCurrYearMnth4(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                        else if (intHistMnth == 5)
                        {
                            UpdateStockCurrYearMnth5(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                        else if (intHistMnth == 6)
                        {
                            UpdateStockCurrYearMnth6(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                        else if (intHistMnth == 7)
                        {
                            UpdateStockCurrYearMnth7(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                        else if (intHistMnth == 8)
                        {
                            UpdateStockCurrYearMnth8(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                        else if (intHistMnth == 9)
                        {
                            UpdateStockCurrYearMnth9(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                        else if (intHistMnth == 10)
                        {
                            UpdateStockCurrYearMnth10(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                        else if (intHistMnth == 11)
                        {
                            UpdateStockCurrYearMnth11(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                        else if (intHistMnth == 12)
                        {
                            UpdateStockCurrYearMnth12(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                        }
                    }
                    else
                    {
                        if (intCurYear - intHistYear == 1)
                        {
                            if (intHistMnth > intCurMnth)
                            {
                                if (intHistMnth == 1)
                                {
                                    UpdateStockCurrYearMnth1(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 2)
                                {
                                    UpdateStockCurrYearMnth2(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 3)
                                {
                                    UpdateStockCurrYearMnth3(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 4)
                                {
                                    UpdateStockCurrYearMnth4(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 5)
                                {
                                    UpdateStockCurrYearMnth5(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 6)
                                {
                                    UpdateStockCurrYearMnth6(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 7)
                                {
                                    UpdateStockCurrYearMnth7(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 8)
                                {
                                    UpdateStockCurrYearMnth8(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 9)
                                {
                                    UpdateStockCurrYearMnth9(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 10)
                                {
                                    UpdateStockCurrYearMnth10(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 11)
                                {
                                    UpdateStockCurrYearMnth11(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 12)
                                {
                                    UpdateStockCurrYearMnth12(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                            }
                            else
                            {
                                if (intHistMnth == 1)
                                {
                                    UpdateStockLYYearMnth1(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 2)
                                {
                                    UpdateStockLYYearMnth2(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 3)
                                {
                                    UpdateStockLYYearMnth3(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 4)
                                {
                                    UpdateStockLYYearMnth4(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 5)
                                {
                                    UpdateStockLYYearMnth5(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 6)
                                {
                                    UpdateStockLYYearMnth6(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 7)
                                {
                                    UpdateStockLYYearMnth7(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 8)
                                {
                                    UpdateStockLYYearMnth8(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 9)
                                {
                                    UpdateStockLYYearMnth9(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 10)
                                {
                                    UpdateStockLYYearMnth10(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 11)
                                {
                                    UpdateStockLYYearMnth11(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                                else if (intHistMnth == 12)
                                {
                                    UpdateStockLYYearMnth12(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                            }
                        }
                        else
                        {
                            if (intCurYear - intHistYear == 2)
                            {
                                if (intHistMnth > intCurMnth)
                                {
                                    if (intHistMnth == 1)
                                    {
                                        UpdateStockLYYearMnth1(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                    else if (intHistMnth == 2)
                                    {
                                        UpdateStockLYYearMnth2(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                    else if (intHistMnth == 3)
                                    {
                                        UpdateStockLYYearMnth3(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                    else if (intHistMnth == 4)
                                    {
                                        UpdateStockLYYearMnth4(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                    else if (intHistMnth == 5)
                                    {
                                        UpdateStockLYYearMnth5(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                    else if (intHistMnth == 6)
                                    {
                                        UpdateStockLYYearMnth6(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                    else if (intHistMnth == 7)
                                    {
                                        UpdateStockLYYearMnth7(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                    else if (intHistMnth == 8)
                                    {
                                        UpdateStockLYYearMnth8(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                    else if (intHistMnth == 9)
                                    {
                                        UpdateStockLYYearMnth9(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                    else if (intHistMnth == 10)
                                    {
                                        UpdateStockLYYearMnth10(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                    else if (intHistMnth == 11)
                                    {
                                        UpdateStockLYYearMnth11(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                    else if (intHistMnth == 12)
                                    {
                                        UpdateStockLYYearMnth12(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                    }
                                }
                                else
                                {
                                    UpdateStockTotSalesHist(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                                }
                            }
                            else
                            {
                                UpdateStockTotSalesHist(history.Qty / sigPackSize, decSalesValue, decTotalCost, history.StockID);
                            }
                        }
                    }
                }
            }

        }

        private static void UpdateStocksCurrentMnthSalesDetails(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFile SET StkSoldCurrMth = @Qty, StkSellCurrMth = @Value, StkCOGSCurrMth = @COGS WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockTotSalesHist(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistTot SET StkCOGSTotal = StkCOGSTotal + @COGS, StkSoldTotal = StkSoldTotal + @Qty, StkSellTotal = StkSellTotal + @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearMnth1(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth1 = @COGS, StkSoldMth1 = @Qty, StkSellMth1 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearMnth2(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth2 = @COGS, StkSoldMth2 = @Qty, StkSellMth2 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearMnth3(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth3 = @COGS, StkSoldMth3 = @Qty, StkSellMth3 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearMnth4(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth4 = @COGS, StkSoldMth4 = @Qty, StkSellMth4 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearMnth5(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth5 = @COGS, StkSoldMth5 = @Qty, StkSellMth5 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearMnth6(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth6 = @COGS, StkSoldMth6 = @Qty, StkSellMth6 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearMnth7(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth7 = @COGS, StkSoldMth7 = @Qty, StkSellMth7 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearMnth8(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth8 = @COGS, StkSoldMth8 = @Qty, StkSellMth8 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearMnth9(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth9 = @COGS, StkSoldMth9 = @Qty, StkSellMth9 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearMnth10(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth10 = @COGS, StkSoldMth10 = @Qty, StkSellMth10 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearMnth11(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth11 = @COGS, StkSoldMth11 = @Qty, StkSellMth11 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearMnth12(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkCOGSMth12 = @COGS, StkSoldMth12 = @Qty, StkSellMth12 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }


        private static void UpdateStockLYYearMnth1(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth1 = @COGS, StkLYSoldMth1 = @Qty, StkLYSellMth1 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockLYYearMnth2(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth2 = @COGS, StkLYSoldMth2 = @Qty, StkLYSellMth2 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYYearMnth3(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth3 = @COGS, StkLYSoldMth3 = @Qty, StkLYSellMth3 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYYearMnth4(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth4 = @COGS, StkLYSoldMth4 = @Qty, StkLYSellMth4 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYYearMnth5(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth5 = @COGS, StkLYSoldMth5 = @Qty, StkLYSellMth5 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYYearMnth6(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth6 = @COGS, StkLYSoldMth6 = @Qty, StkLYSellMth6 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYYearMnth7(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth7 = @COGS, StkLYSoldMth7 = @Qty, StkLYSellMth7 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYYearMnth8(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth8 = @COGS, StkLYSoldMth8 = @Qty, StkLYSellMth8 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYYearMnth9(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth9 = @COGS, StkLYSoldMth9 = @Qty, StkLYSellMth9 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYYearMnth10(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth10 = @COGS, StkLYSoldMth10 = @Qty, StkLYSellMth10 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYYearMnth11(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth11 = @COGS, StkLYSoldMth11 = @Qty, StkLYSellMth11 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYYearMnth12(double Qty, decimal Value, decimal COGS, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYCOGSMth12 = @COGS, StkLYSoldMth12 = @Qty, StkLYSellMth12 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@COGS", COGS);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
    }
}
